package com.arges.web.dao;

import java.util.List;

import javax.sql.DataSource;

import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Required;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.simple.ParameterizedBeanPropertyRowMapper;
import org.springside.modules.orm.hibernate.Page;

import com.mchange.v2.c3p0.ComboPooledDataSource;

/**
 * jdbc封装类 描述: springJDBC封装类
 * 
 * @author arges
 *
 */
public class PaginationJdbcSupport<T>
{

	protected JdbcTemplate jdbcTemplate;

	protected String dbType = "com.mysql.jdbc.Driver";

	@Required
	public void setDataSource(DataSource dataSource)
	{
		jdbcTemplate = new JdbcTemplate(dataSource);
		ComboPooledDataSource ds = (ComboPooledDataSource) dataSource;
		String driverClass = ds.getDriverClass();
		if ("com.mysql.jdbc.Driver".equals(driverClass))
		{
			dbType = "com.mysql.jdbc.Driver";
		}
	}

	/**
	 * 获取分页对象
	 * 
	 * @param page 分页对象
	 * @param clazz 反射对象
	 * @param sql
	 * @param values 条件
	 * @return
	 */
	public Page<T> find(Page<T> page, Class<T> clazz, String sql, Object... values)
	{
		if (page.isAutoCount())
		{
			page.setTotalCount(countQueryResult(sql, values));
			// 不做处理 上下级会有问题，前端页面已做控制
			// int totalPages = page.getTotalPages();
			// if (page.getPageNo() > totalPages && totalPages != 0) {
			// page.setPageNo(page.getTotalPages());
			// }
		}
		page.setResult(getResult(page, clazz, sql, values));
		return page;
	}

	/**
	 * 
	 * findAndRowId:(查询结果带ROWID,并且可传参查询)
	 * 
	 * @param page
	 * @param clazz
	 * @param sql
	 * @param rowId
	 * @return
	 * @throws
	 * @since Ver 1.1
	 */
	public Page<T> findAndRowId(Page<T> page, Class<T> clazz, String sql, String rowId)
	{
		if (page.isAutoCount())
		{
			page.setTotalCount(countQueryResult(sql));
			// int totalPages = page.getTotalPages();
			// if (page.getPageNo() > totalPages && totalPages != 0) {
			// page.setPageNo(page.getTotalPages());
			// }
		}
		page.setResult(getResultRowId(page, clazz, sql, rowId));
		return page;
	}

	/**
	 * 
	 * getResultRowId:(区分数据类型查询)
	 * 
	 * @param page
	 * @param clazz
	 * @param sql
	 * @param rowId
	 * @return
	 * @throws
	 * @since Ver 1.1
	 */
	private List<T> getResultRowId(Page<T> page, Class<T> clazz, String sql, String rowId)
	{
		List<T> resultList = null;
		// if (Constants.ORCALE_DRIVER_CLASS.equals(dbType))
		// {// 表示数据库类型是oracle
		// resultList = getResultRowIdOracle(page, clazz, sql, rowId);
		// }
		// else if (Constants.MY_DRIVER_CLASS.equals(dbType))
		// {// 表示数据库类型是mysql
		resultList = getResultAndRowIdMySQL(page, clazz, sql, rowId);
		// }
		return resultList;

	}

	/**
	 * 获取List
	 * 
	 * @param page 分页对象
	 * @param clazz 反射对象
	 * @param sql
	 * @param values 条件
	 * @return
	 */
	private List<T> getResult(Page<T> page, Class<T> clazz, String sql, Object[] values)
	{
		List<T> resultList = null;
		// if (Constants.ORCALE_DRIVER_CLASS.equals(dbType))
		// {// 表示数据库类型是oracle
		// resultList = getResultOracle(page, clazz, sql, values);
		// }
		// else if (Constants.MY_DRIVER_CLASS.equals(dbType))
		// {// 表示数据库类型是mysql
		resultList = getResultMySql(page, clazz, sql, values);
		// }
		return resultList;
	}

	/**
	 * 总条数
	 * 
	 * @param sql
	 * @param values 条件
	 * @return
	 */
	public int countQueryResult(String sql, Object... values)
	{
		StringBuffer totalSQL = new StringBuffer(" SELECT count(*) FROM ( ");
		totalSQL.append(sql);
		totalSQL.append(" ) totalTable ");
		// System.out.println(totalSQL.toString());
		return jdbcTemplate.queryForInt(totalSQL.toString(), values);
	}

	/**
	 * ORACLE 分页查询方法
	 * 
	 * @param page
	 * @param clazz
	 * @param sql
	 * @param values
	 * @return
	 */
	public List<T> getResultOracle(Page<T> page, Class<T> clazz, String sql, Object... values)
	{
		StringBuffer paginationSQL = new StringBuffer(" SELECT * FROM ( ");
		paginationSQL.append(" SELECT temp.* ,ROWNUM num FROM ( ");
		paginationSQL.append(sql);
		addOrderBy(paginationSQL, page);
		paginationSQL.append("  ) temp where ROWNUM <= " + (page.getFirst() + page.getPageSize()));
		paginationSQL.append(" ) WHERE  num > " + page.getFirst());
		return jdbcTemplate.query(paginationSQL.toString(), ParameterizedBeanPropertyRowMapper.newInstance(clazz),
				values);
	}

	/**
	 * 
	 * getResultRowIdOracle:(根据ROWID查询)
	 * 
	 * @param page
	 * @param clazz
	 * @param sql
	 * @param rowId
	 * @return
	 * @throws
	 * @since Ver 1.1
	 */
	public List<T> getResultRowIdOracle(Page<T> page, Class<T> clazz, String sql, String rowId)
	{
		StringBuffer paginationSQL = new StringBuffer(" SELECT * FROM ( ");
		paginationSQL.append(" SELECT temp.* ,ROWNUM num FROM ( ");
		paginationSQL.append(sql);
		addOrderBy(paginationSQL, page);
		paginationSQL.append("  ) temp where ROWNUM <= " + (page.getFirst() + page.getPageSize()));
		paginationSQL.append(" ) WHERE  num > " + page.getFirst());
		if (rowId != null && !"".equals(rowId))
		{
			paginationSQL.append("and num in(").append(rowId).append(")");
		}
		return jdbcTemplate.query(paginationSQL.toString(), ParameterizedBeanPropertyRowMapper.newInstance(clazz));
	}

	/**
	 * MYSQL分页查询方法
	 * 
	 * @param page
	 * @param clazz
	 * @param sql
	 * @param values
	 * @return
	 */
	public List<T> getResultMySql(Page<T> page, Class<T> clazz, String sql, Object... values)
	{
		StringBuffer paginationSQL = new StringBuffer(sql);
		addOrderBy(paginationSQL, page);
		paginationSQL.append(" limit ").append(page.getFirst()).append(",").append(page.getPageSize());
		// System.out.println(paginationSQL.toString());
		return jdbcTemplate.query(paginationSQL.toString(), ParameterizedBeanPropertyRowMapper.newInstance(clazz),
				values);
	}

	/**
	 * 
	 * addOrderBy:(查询排序增加)
	 *
	 * @param paginationSQL
	 * @param page
	 * @throws
	 * @since Ver 1.1
	 */
	private void addOrderBy(StringBuffer paginationSQL, Page<T> page)
	{
		if (!StringUtils.isEmpty(page.getOrderBy()))
			paginationSQL.append(" order by ").append(page.getOrderBy()).append(" ").append(page.getOrder());
	}

	/**
	 * 
	 * getResultAndRowIdMySQL:(MYSQL获取ROWID)
	 * 
	 * @param page
	 * @param clazz
	 * @param sql
	 * @param rowId
	 * @return
	 * @throws
	 * @since Ver 1.1
	 */
	public List<T> getResultAndRowIdMySQL(Page<T> page, Class<T> clazz, String sql, String rowId)
	{
		StringBuffer paginationSQL = new StringBuffer(sql);
		addOrderBy(paginationSQL, page);
		StringBuffer sqlAll = new StringBuffer();
		paginationSQL.append(" limit ").append(page.getFirst()).append(",").append(page.getPageSize());
		sqlAll.append(" select temp.* from (SELECT @rowid:=@rowid+1 as num,t.* from (").append(paginationSQL)
				.append(")t,(select @rowid:=0)as init )temp ");
		if (rowId != null && !"".equals(rowId))
		{
			sqlAll.append("where temp.num in(").append(rowId).append(")");
		}
		return jdbcTemplate.query(sqlAll.toString(), ParameterizedBeanPropertyRowMapper.newInstance(clazz));
	}

	/**
	 * 根据不同数据库类型获得结果集
	 * 
	 * @param clazz
	 * @param sql
	 * @param values
	 * @return
	 */
	public List<T> getResult(Class<T> clazz, String sql, Object... values)
	{
		return jdbcTemplate.query(sql, ParameterizedBeanPropertyRowMapper.newInstance(clazz), values);
	}

	/**
	 * 
	 * saveEntity:(这里描述这个方法的作用、使用方法、注意事项)
	 * 
	 * @param sql
	 * @param values
	 * @return
	 * @throws
	 * @since Ver 1.1
	 */
	public int saveEntity(String sql, Object... values)
	{
		values[0] = getSequence(values[0]);
		return jdbcTemplate.update(sql, values);

	}

	/**
	 * 
	 * getSequence:(这里描述这个方法的作用、使用方法、注意事项)
	 * 
	 * @param sequenceName
	 * @return
	 * @throws
	 * @since Ver 1.1
	 */
	public Object getSequence(Object sequenceName)
	{
		Object squenceId = null;
		if ("oracle.jdbc.driver.OracleDriver".equals(dbType))
		{// 表示数据库类型是oracle
			squenceId = sequenceName + ".nextval";
		}
		return squenceId;
	}

	/**
	 * 
	 * getDateFunction:(获取数据库的时间函数Mysql和Oracle不同)
	 * 
	 * @param date
	 * @param dateFormat
	 * @return
	 * @throws
	 * @since Ver 3.0
	 */
	public String getDateFunction(String date, String dateFormat)
	{
		String result = "";
		// if (Constants.ORCALE_DRIVER_CLASS.equals(dbType))
		// {
		// result = "to_date('" + date + "','" + dateFormat + "')";
		// }
		// else if (Constants.MY_DRIVER_CLASS.equals(dbType))
		// {
		if ("yyyy-MM-dd hh24:mi:ss".equals(dateFormat))
		{
			result = "str_to_date('" + date + "','%Y-%m-%d %H:%i:%s')";
		}
		else if ("yyyy-MM-dd".equals(dateFormat))
		{
			result = "str_to_date('" + date + "','%Y-%m-%d')";
		}
		// }
		return result;
	}

	public String getToCharFunction(String date, String dateFormat)
	{
		return getToCharFunction(date, dateFormat, false);
	}

	public String getToCharFunction(String date, String dateFormat, boolean flag)
	{
		String result = "";
		// if (Constants.ORCALE_DRIVER_CLASS.equals(dbType))
		// {
		// if (dateFormat == null)
		// {
		// result = "to_char(" + date + ")";
		// }
		// else
		// {
		// result = "to_char(" + date + ",'" + dateFormat + "')";
		// }
		// }
		// else if (Constants.MY_DRIVER_CLASS.equals(dbType))
		// {
		if (dateFormat == null)
		{
			result = date;
		}
		else if ("yyyy-MM-dd hh24:mi:ss".equals(dateFormat))
		{
			result = "date_format(" + date + ",'%Y-%m-%d %H:%i:%s')";
		}
		else if ("yyyy-MM-dd".equals(dateFormat) && flag)
		{
			result = "date_format(" + date + ",'%Y-%m-%d')";
		}
		else if ("yyyy-MM".equals(dateFormat) && flag)
		{
			result = "date_format(" + date + ",'%Y-%m')";
		}
		else if ("hh24:mi:ss".equals(dateFormat))
		{
			result = "date_format(" + date + ",'%H:%i:%s')";
		}
		else
		{// mysql中dateFormat是yyyy-MM-dd的必须走这里，否则流量统计的月报和年报会有问题
			result = date;
		}
		// }
		return result;

	}

	/**
	 * 
	 * getSysdate:(获取数据库的系统时间Mysql和Oracle不同)
	 * 
	 * @return
	 * @throws
	 * @since Ver 3.0
	 */
	public String getSysdate()
	{
		String result = "";
		// if (Constants.ORCALE_DRIVER_CLASS.equals(dbType))
		// {
		// result = "sysdate";
		// }
		// else if (Constants.MY_DRIVER_CLASS.equals(dbType))
		// {
		result = "sysdate()";
		// }
		return result;
	}

	/**
	 * 
	 * getDateFunction:(获取数据库的时间函数Mysql和Oracle不同)
	 * 
	 * @param date
	 * @param dateFormat
	 * @return
	 * @throws
	 * @since Ver 3.0
	 */
	public String addDateFunction(String date, String dateFormat, int num, String dateType)
	{
		String result = "";
		// if (Constants.ORCALE_DRIVER_CLASS.equals(dbType))
		// {
		// if ("day".equals(dateType))
		// {
		// // szj 2013-01-29 修改时间+1
		// result = getDateFunction(date, dateFormat) + "+" + num;
		// }
		// else if ("month".equals(dateType))
		// {
		// result = "add_months(" + getDateFunction(date, dateFormat) + "," + num + ")";
		// }
		// else if ("year".equals(dateType))
		// {
		// result = "add_months(" + getDateFunction(date, dateFormat) + "," + 12 * num + ")";
		// }
		// }
		// else if (Constants.MY_DRIVER_CLASS.equals(dbType))
		// {
		result = "DATE_ADD('" + date + "',interval " + num + " " + dateType + ")";
		// }
		return result;
	}

	/**
	 * 
	 * sqlEscapeSpecialCharacter:(这里描述这个方法的作用、使用方法、注意事项)
	 *
	 * @return
	 * @throws
	 * @since Ver 1.1
	 */
	public String sqlEscapeSpecialCharacter(String value)
	{
		String appendSql = "";
		if (!StringUtils.isEmpty(value) && value.contains("\\") && "oracle.jdbc.driver.OracleDriver".equals(dbType))
		{
			appendSql = " escape '\\'";
		}
		return appendSql;
	}

	/**
	 * 
	 * getDateTruncFunction:(截取时间到某一位)
	 *
	 * @param date
	 * @param dateType
	 * @return
	 * @throws
	 * @since Ver 1.1
	 */
	public String getDateTruncFunction(String date, String dateType)
	{
		String result = "";
		if (StringUtils.isEmpty(dateType))
		{
			dateType = "dd";
		}
		// if (Constants.ORCALE_DRIVER_CLASS.equals(dbType))
		// {
		// result = " trunc(" + date + ", '" + dateType + "') ";
		// }
		// else if (Constants.MY_DRIVER_CLASS.equals(dbType))
		// {
		if (("dd").equals(dateType))
		{
			result = " DATE_FORMAT(" + date + ",'%Y-%m-%d')";
		}
		else if (("MM").equals(dateType))
		{
			result = " DATE_FORMAT(" + date + ",'%Y-%m')";
		}
		else if (("yyyy").equals(dateType))
		{
			result = " DATE_FORMAT(" + date + ",'%Y')";
		}
		// }
		return result;
	}

	public String getSubStrFunction(String str, Integer start, Integer length)
	{
		String result = "";
		// if (Constants.ORCALE_DRIVER_CLASS.equals(dbType))
		// {
		// result = " substr(" + str + "," + start + "," + length + ") ";
		// }
		// else if (Constants.MY_DRIVER_CLASS.equals(dbType))
		// {
		result = " substr(" + str + "," + ++start + "," + length + ") ";
		// }
		return result;

	}
}
